

*open the police arrest data with victim information
use "W:\original\u1067_al3_rpk.dta", clear

*Renaming variables below
rename rnimi crime /*crime name*/
rename rkdi	crimecode6
rename juttu_ind case_index
rename ao_ika	age_plaintiff /*age of plaintiff*/


rename re_sukup	suspect_sex
rename re_ika age_suspect 

rename ao_sukup	plaintiff_sex
rename juttu_koodi1	economic /*case  code 1, see description (police)*/
rename juttu_koodi2	race
rename juttu_koodi3 domestic
rename juttu_koodi4 it
rename juttu_koodi5 drug  


*crime codes for workplace crimes
gen pot_domestic=0
replace pot_domestic=1 if crimecode6=="200101" |  crimecode6=="200104" | crimecode6== "200183" | crimecode6== "200184" | crimecode6== "200201" | crimecode6== "200202" | crimecode6== "200401" | crimecode6== "200402" | crimecode6== "200501" | crimecode6== "200503" | crimecode6== "2005A1" | crimecode6== "200601" | crimecode6== "200603" | crimecode6== "200701" | crimecode6== "200702" | crimecode6== "200801" | crimecode6== "200804" | crimecode6== "2008A1" | crimecode6== "2008A3" | crimecode6== "2008B1" | crimecode6== "2008B3" | crimecode6== "2008C1" | crimecode6== "2008C2" | crimecode6== "2507A1" | crimecode6==  "250801" | crimecode6==  "250301" | crimecode6==  "2503A1" | crimecode6==  "2503A3"| crimecode6==  "250101"  | crimecode6==  "250201" | crimecode6==  "250303" | crimecode6==  "250701" | crimecode6==   "310101" | crimecode6==  "310102" | crimecode6==  "310301" | crimecode6==  "310302"| crimecode6==  "210101" | crimecode6== "210102" | crimecode6== "210201" | crimecode6==  "210202" | crimecode6== "210301" | crimecode6== "210302" | crimecode6== "210401" | crimecode6== "210402" | crimecode6==  "210501" | crimecode6== "210502" | crimecode6== "210601" | crimecode6== "210602" | crimecode6== "2106A1" | crimecode6== "210701" | crimecode6== "210801" | crimecode6== "210901" | crimecode6== "211001" | crimecode6== "211101"| crimecode6== "211201" | crimecode6== "211301" | crimecode6==  "211401" | crimecode6== "211501" | crimecode6=="2503B1" | crimecode6=="2503B2" | crimecode6=="2503C1" | crimecode6=="470301" | crimecode6=="4703A1" | crimecode6=="111101"


tab pot_dom
count if pot_dom==1 & ao_shnro!="" & re_shnro!=""
tab dom if  pot_dom==1

*Renaming variables below
rename ilmoitusvuosi year_announcement
rename kuittausvuosi year_record


gen dom=domestic!=""

keep if (dom==1 | pot_dom==1) & ao_shnro!="" & re_shnro!=""
duplicates drop

gen pkupvm_reported=pkupvm!=""
gsort s_tapausid re_shnro ao_shnro taika pkupvm_reported crimecode6 year_event pkupvm_reported

duplicates report s_tapausid re_shnro ao_shnro taika crimecode6 year_event s_juttu

 
duplicates drop s_tapausid re_shnro ao_shnro taika crimecode6 year_event, force

rename  taika	date_event
rename s_tapausid	case_id
gen year_event=substr(date_event,-4,.)
destring year_event, replace


save "W:\workplace\data\police_workplace", replace


